package com.show.service.emp;


import com.show.util.PageUtil;
import com.show.util.QueryUtil;
import com.show.dto.sys_emp.SysEmp;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.List;

/**
 * @author yxd
 * @Param
 * @date 2017-10-16 15:13
 */
@Service
@Transactional
public class CommonService {
    @Autowired
    private QueryUtil queryUtil;



    /**
     * 查询用户、部门、角色相关信息（分页）
     *
     * @author yxd
     * @Param 用户编号，用户名称，角色名称，部门名称，页码，页容
     * @date 2017-10-23 13:11
     */
    public PageUtil<SysEmp> findAllEmpByPage(SysEmp emp, Integer page, Integer pageSize) {
        StringBuffer sql = new StringBuffer();
        if (null == page || "".equals(page) || "0".equals(page)) {//排空，设置默认值
            page = 1;
        }
        if (null == pageSize || "".equals(pageSize) || "0".equals(pageSize)) {//排空，设置默认值
            pageSize = 10;
        }
        sql.append("SELECT * FROM sys_emp e where 1=1 ");

        if(StringUtils.isNotBlank(emp.getLoginName())){
            sql.append(" and e.login_name like '%"+emp.getLoginName()+"%' ");
        }
        if(StringUtils.isNotBlank(emp.getEmpName())){
            sql.append(" and e.emp_name like '%"+emp.getEmpName()+"%' ");
        }
        if(StringUtils.isNotBlank(emp.getOrgName())){
            sql.append(" and EXISTS (select 'X' from sys_emp_org eo,sys_org o where eo.emp_id=e.emp_id and o.org_id=eo.org_id and o.org_name like '%"+emp.getOrgName()+"%') ");
        }
        if(StringUtils.isNotBlank(emp.getRoleId())){
            sql.append(" and EXISTS (select 'X' from sys_emp_role er  where er.emp_id=e.emp_id and er.role_id='"+emp.getRoleId()+"') ");
        }
        if(StringUtils.isNotBlank(emp.getSts())){
            sql.append(" and e.sts ='0' ");
        }else{
            sql.append(" and e.sts ='1' ");
        }

        PageUtil<SysEmp> emps = queryUtil.queryByPage(sql.toString(), SysEmp.class, page, pageSize);//分页查询
        return emps;
    }

    /**
     * 查询用户、部门、角色相关信息（分页）
     * 查询假期相关信息（分页）
     *
     * @author 姜文
     * @date 2018年2月1日, PM 03:07:50
     */
    public List<SysEmp> findEmpByOrgId(String orgId) {
        StringBuffer sql = new StringBuffer();
        sql.append(this.getQueryEmpOrgSql());
        sql.append(" and o.org_id = '"+ orgId +"'");
        List<SysEmp> cgEmpList = queryUtil.query(sql.toString(), SysEmp.class);
        return cgEmpList;
    }

    public StringBuffer getQueryEmpOrgSql() {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\te.emp_id AS empId,\n" +
                "\te.email AS email,\n" +
                "\te.emp_code AS empCode,\n" +
                "\te.emp_name AS empName,\n" +
                "\te.login_name AS loginName,\n" +
                "\te.login_pwd AS loginPwd,\n" +
                "\te.gender AS gender,\n" +
                "\te.sts AS sts,\n" +
                "\te.contact_number AS contactNumber,\n" +
                "\te.cdate AS cdate,\n" +
                "\to.org_id AS orgId,\n" +
                "\to.org_name AS orgName,\n" +
                "\to.org_flag AS orgFlag,\n" +
                "\to.parent_org_id AS parentOrgId,\n" +
                "\to.parent_org_name AS parentOrgName\n" +
                "FROM\n" +
                "\tSys_emp e,\n" +
                "\tSys_org o\n" +
                "WHERE\n" +
                "\te.org_id = o.org_id\n" +
                "AND e.sts = 1\n");
        return sql;
    }


    /**
     *
     *根据emp_id查找角色名
     * @author yxd
     * @date 2018年2月6日
     */
    public List<SysEmp> findRoleByEmpId(int empId) {
        StringBuffer sql = new StringBuffer();
        sql.append("select GROUP_CONCAT(r.role_name) as role_name,GROUP_CONCAT(r.role_id) as role_id " +
                "from sys_emp_role er,sys_role r " +
                "where er.role_id=r.role_id and er.emp_id="+empId+"");
        List<SysEmp> cgEmpList = queryUtil.query(sql.toString(), SysEmp.class);
        return cgEmpList;
    }


    /**
     *
     *根据emp_id查找部门名
     * @author yxd
     * @date 2018年2月6日
     */
    public List<SysEmp> findOrgByEmpId(int empId) {
        StringBuffer sql = new StringBuffer();
        sql.append("select GROUP_CONCAT(o.org_name) as org_name,GROUP_CONCAT(o.org_id) as org_id " +
                "from sys_emp_org eo,sys_org o " +
                "where eo.org_id=o.org_id and eo.emp_id="+empId+"");
        List<SysEmp> cgEmpList = queryUtil.query(sql.toString(), SysEmp.class);
        return cgEmpList;
    }

}
